import Dexie from 'dexie';

// 定义数据库
class LotteryDatabase extends Dexie {
  constructor() {
    super('LotteryDatabase');
    
    // 定义数据库结构
    this.version(1).stores({
      selections: '++id, redBalls, blueBall, createTime, note',
      lotteryHistory: '++id, issueno, number, refernumber, opendate, deadline, saleamount, totalmoney, prize, createTime'
    });
    
    // 升级到版本2，添加更多字段
    this.version(2).stores({
      selections: '++id, redBalls, blueBall, createTime, note',
      lotteryHistory: '++id, caipiaoid, issueno, number, refernumber, opendate, officialopendate, opentime, opendatetime, deadline, saleamount, totalmoney, prize, createTime'
    });
    
    // 升级到版本3，添加统一的选号记录表
    this.version(3).stores({
      selections: '++id, redBalls, blueBall, createTime, note', // 保留旧表兼容性
      lotteryHistory: '++id, caipiaoid, issueno, number, refernumber, opendate, officialopendate, opentime, opendatetime, deadline, saleamount, totalmoney, prize, createTime',
      pickingRecords: '++id, redBalls, blueBalls, algorithm, source, sourceName, timestamp, createTime, note, predicted' // 新的统一选号记录表
    });
  }
}

// 创建数据库实例
const db = new LotteryDatabase();

// 数据库操作类
export class DatabaseService {
  
  // 添加选号记录
  static async addSelection(redBalls, blueBall, note = '') {
    try {
      // 验证数据
      if (!Array.isArray(redBalls) || redBalls.length !== 6) {
        throw new Error('红球必须是6个数字的数组');
      }
      
      if (!blueBall || blueBall < 1 || blueBall > 16) {
        throw new Error('蓝球必须是1-16之间的数字');
      }
      
      // 验证红球范围
      for (let ball of redBalls) {
        if (!ball || ball < 1 || ball > 33) {
          throw new Error('红球必须是1-33之间的数字');
        }
      }
      
      // 检查红球是否有重复
      const uniqueRedBalls = [...new Set(redBalls)];
      if (uniqueRedBalls.length !== 6) {
        throw new Error('红球不能有重复数字');
      }
      
      const selection = {
        redBalls: redBalls.sort((a, b) => a - b), // 排序存储
        blueBall: blueBall,
        createTime: new Date().toISOString(),
        note: note || ''
      };
      
      const id = await db.selections.add(selection);
      return { id, ...selection };
    } catch (error) {
      console.error('添加选号记录失败:', error);
      throw error;
    }
  }
  
  // 获取所有选号记录
  static async getAllSelections() {
    try {
      const selections = await db.selections.orderBy('createTime').reverse().toArray();
      return selections;
    } catch (error) {
      console.error('获取选号记录失败:', error);
      throw error;
    }
  }
  
  // 删除选号记录
  static async deleteSelection(id) {
    try {
      await db.selections.delete(id);
      return true;
    } catch (error) {
      console.error('删除选号记录失败:', error);
      throw error;
    }
  }
  
  // 清空所有记录
  static async clearAllSelections() {
    try {
      await db.selections.clear();
      return true;
    } catch (error) {
      console.error('清空选号记录失败:', error);
      throw error;
    }
  }
  
  // 获取记录总数
  static async getSelectionCount() {
    try {
      const count = await db.selections.count();
      return count;
    } catch (error) {
      console.error('获取记录总数失败:', error);
      throw error;
    }
  }
  
  // 根据ID获取单个记录
  static async getSelectionById(id) {
    try {
      const selection = await db.selections.get(id);
      return selection;
    } catch (error) {
      console.error('获取选号记录失败:', error);
      throw error;
    }
  }
  
  // 更新记录备注
  static async updateSelectionNote(id, note) {
    try {
      await db.selections.update(id, { note: note });
      return true;
    } catch (error) {
      console.error('更新记录备注失败:', error);
      throw error;
    }
  }

  // ========== 双色球历史记录相关方法 ==========
  
  // 添加双色球开奖记录
  static async addLotteryHistory(historyData) {
    try {
      if (!historyData || !historyData.issueno) {
        console.error('无效的历史记录数据:', historyData);
        return null;
      }

      // 检查是否已存在该期号的记录
      const existing = await db.lotteryHistory.where('issueno').equals(historyData.issueno).first();
      if (existing) {
        console.log(`期号 ${historyData.issueno} 已存在，跳过添加`);
        return null; // 返回null表示没有新增记录
      }

      // 使用开奖记录的实际时间作为createTime，优先级：opendatetime > opendate > 当前时间
      let createTime;
      if (historyData.opendatetime) {
        createTime = new Date(historyData.opendatetime).toISOString();
      } else if (historyData.opendate) {
        // 如果只有日期，设置为当天21:15（双色球开奖时间）
        const openDate = new Date(historyData.opendate);
        openDate.setHours(21, 15, 0, 0);
        createTime = openDate.toISOString();
      } else {
        createTime = new Date().toISOString();
      }

      const record = {
        ...historyData,
        createTime: createTime
      };

      console.log(`正在添加新记录到数据库:`, record);
      const id = await db.lotteryHistory.add(record);
      console.log(`成功添加记录，ID: ${id}`);
      
      return { id, ...record };
    } catch (error) {
      console.error('添加开奖记录失败:', error);
      console.error('失败的数据:', historyData);
      throw error;
    }
  }

  // 批量添加双色球开奖记录
  static async addLotteryHistoryBatch(historyList) {
    try {
      const results = [];
      for (const historyData of historyList) {
        const result = await this.addLotteryHistory(historyData);
        results.push(result);
      }
      return results;
    } catch (error) {
      console.error('批量添加开奖记录失败:', error);
      throw error;
    }
  }

  // 获取所有双色球开奖记录（最多36条，按日期从新到旧排序）
  static async getAllLotteryHistory() {
    try {
      // 先尝试按createTime排序，如果失败则按id排序
      let records;
      try {
        records = await db.lotteryHistory.orderBy('createTime').reverse().limit(36).toArray();
      } catch (sortError) {
        console.warn('按createTime排序失败，使用opendate排序:', sortError);
        try {
          records = await db.lotteryHistory.orderBy('opendate').reverse().limit(36).toArray();
        } catch (sortError2) {
          console.warn('按opendate排序失败，使用id排序:', sortError2);
          records = await db.lotteryHistory.orderBy('id').reverse().limit(36).toArray();
        }
      }
      
      // 如果还是没有数据，尝试直接获取所有记录
      if (!records || records.length === 0) {
        records = await db.lotteryHistory.toArray();
        // 手动排序并限制数量
        records.sort((a, b) => {
          const dateA = new Date(a.createTime || a.opendate || 0);
          const dateB = new Date(b.createTime || b.opendate || 0);
          return dateB - dateA; // 降序排列
        });
        // 限制最多36条记录
        records = records.slice(0, 36);
      }
      
      console.log('获取到的历史记录数量:', records.length, '(最多显示36条)');
      return records;
    } catch (error) {
      console.error('获取开奖记录失败:', error);
      throw error;
    }
  }

  // 根据期号获取开奖记录
  static async getLotteryHistoryByIssue(issueno) {
    try {
      const record = await db.lotteryHistory.where('issueno').equals(issueno).first();
      return record;
    } catch (error) {
      console.error('根据期号获取开奖记录失败:', error);
      throw error;
    }
  }

  // 获取最近N期的开奖记录
  static async getRecentLotteryHistory(count = 30) {
    try {
      let records;
      try {
        records = await db.lotteryHistory
          .orderBy('createTime')
          .reverse()
          .limit(count)
          .toArray();
      } catch (sortError) {
        console.warn('按createTime排序失败，使用备用方案:', sortError);
        records = await db.lotteryHistory
          .orderBy('id')
          .reverse()
          .limit(count)
          .toArray();
      }
      
      console.log('获取到的最近记录数量:', records.length);
      return records;
    } catch (error) {
      console.error('获取最近开奖记录失败:', error);
      throw error;
    }
  }

  // 获取开奖记录总数
  static async getLotteryHistoryCount() {
    try {
      const count = await db.lotteryHistory.count();
      return count;
    } catch (error) {
      console.error('获取开奖记录总数失败:', error);
      throw error;
    }
  }

  // 清空所有开奖记录
  static async clearAllLotteryHistory() {
    try {
      await db.lotteryHistory.clear();
      return true;
    } catch (error) {
      console.error('清空开奖记录失败:', error);
      throw error;
    }
  }

  // 检查期号是否已存在
  static async isIssueExists(issueno) {
    try {
      const record = await db.lotteryHistory.where('issueno').equals(issueno).first();
      return !!record;
    } catch (error) {
      console.error('检查期号是否存在失败:', error);
      throw error;
    }
  }

  // 清理旧记录，保持最多36条记录
  static async cleanupOldRecords() {
    try {
      const totalCount = await db.lotteryHistory.count();
      console.log('当前记录总数:', totalCount);
      
      if (totalCount <= 36) {
        console.log('记录数量未超过限制，无需清理');
        return { cleaned: 0, remaining: totalCount };
      }
      
      // 获取所有记录并排序
      let allRecords = await db.lotteryHistory.toArray();
      allRecords.sort((a, b) => {
        const dateA = new Date(a.createTime || a.opendate || 0);
        const dateB = new Date(b.createTime || b.opendate || 0);
        return dateB - dateA; // 降序排列，最新的在前
      });
      
      // 保留最新的36条，删除其余的
      const recordsToKeep = allRecords.slice(0, 36);
      const recordsToDelete = allRecords.slice(36);
      
      console.log(`准备删除 ${recordsToDelete.length} 条旧记录，保留最新的 ${recordsToKeep.length} 条`);
      
      // 删除旧记录
      for (const record of recordsToDelete) {
        await db.lotteryHistory.delete(record.id);
      }
      
      console.log(`清理完成，删除了 ${recordsToDelete.length} 条旧记录`);
      return { cleaned: recordsToDelete.length, remaining: recordsToKeep.length };
    } catch (error) {
      console.error('清理旧记录失败:', error);
      throw error;
    }
  }

  // 重置数据库（用于调试）
  static async resetDatabase() {
    try {
      console.log('开始重置数据库...');
      
      // 清空所有表
      await db.lotteryHistory.clear();
      await db.selections.clear();
      
      console.log('数据库重置完成');
      return true;
    } catch (error) {
      console.error('重置数据库失败:', error);
      throw error;
    }
  }

  // 初始化数据库（添加示例数据）
  static async initializeDatabase() {
    try {
      console.log('开始初始化数据库...');
      
      // 检查是否已有数据
      const count = await this.getLotteryHistoryCount();
      if (count > 0) {
        console.log('数据库已有数据，跳过初始化');
        return false;
      }
      
      // 添加一些示例数据（包含一些重复号码用于测试前三期重复检查）
      const sampleData = [
        {
          caipiaoid: 11,
          issueno: "2025006",
          number: "03 04 17 19 25 27",
          refernumber: "14",
          opendate: "2025-01-14",
          officialopendate: "",
          opentime: "21:15:00",
          opendatetime: "2025-01-14T21:15:00.000Z",
          deadline: "2025-03-15",
          saleamount: 381527438,
          totalmoney: "2443475122.00",
          createTime: new Date('2025-01-14T21:15:00.000Z'),
          prize: [
            {
              prizename: "一等奖",
              require: "中6+1",
              num: 5,
              singlebonus: 8000000
            }
          ]
        },
        {
          caipiaoid: 11,
          issueno: "2025005",
          number: "06 11 12 21 27 28",
          refernumber: "15",
          opendate: "2025-01-12",
          officialopendate: "",
          opentime: "21:15:00",
          opendatetime: "2025-01-12T21:15:00.000Z",
          deadline: "2025-03-13",
          saleamount: 358843770,
          totalmoney: "2427919422.00",
          createTime: new Date('2025-01-12T21:15:00.000Z'),
          prize: [
            {
              prizename: "一等奖",
              require: "中6+1",
              num: 3,
              singlebonus: 9000000
            }
          ]
        },
        {
          caipiaoid: 11,
          issueno: "2025004",
          number: "01 08 15 19 25 32",
          refernumber: "12",
          opendate: "2025-01-09",
          officialopendate: "",
          opentime: "21:15:00",
          opendatetime: "2025-01-09T21:15:00.000Z",
          deadline: "2025-03-10",
          saleamount: 342156890,
          totalmoney: "2398765432.00",
          createTime: new Date('2025-01-09T21:15:00.000Z'),
          prize: [
            {
              prizename: "一等奖",
              require: "中6+1",
              num: 2,
              singlebonus: 10000000
            }
          ]
        }
      ];
      
      for (const data of sampleData) {
        await this.addLotteryHistory(data);
      }
      
      console.log('数据库初始化完成，添加了示例数据');
      return true;
    } catch (error) {
      console.error('初始化数据库失败:', error);
      throw error;
    }
  }

  // 重新初始化数据库（修复createTime问题）
  static async reinitializeDatabase() {
    try {
      console.log('开始重新初始化数据库以修复createTime问题...');
      
      // 重置数据库
      await this.resetDatabase();
      
      // 重新初始化
      await this.initializeDatabase();
      
      console.log('数据库重新初始化完成');
      return true;
    } catch (error) {
      console.error('重新初始化数据库失败:', error);
      throw error;
    }
  }

  // ==================== 统一选号记录管理 ====================
  
  /**
   * 添加选号记录到统一表
   * @param {Array} redBalls - 红球数组 [1,2,3,4,5,6]
   * @param {Array} blueBalls - 蓝球数组 [7] 或 [7,8]
   * @param {string} algorithm - 算法类型: random, hot, cold, smart, calculator, manual
   * @param {string} source - 来源: smart_picker, calculator, manual
   * @param {string} note - 备注
   * @param {boolean} predicted - 是否为预测记录
   */
  static async addPickingRecord(redBalls, blueBalls, algorithm, source, note = '', predicted = false) {
    try {
      // 验证数据
      if (!Array.isArray(redBalls) || redBalls.length !== 6) {
        throw new Error('红球必须是6个数字的数组');
      }
      
      if (!Array.isArray(blueBalls) || blueBalls.length === 0) {
        throw new Error('蓝球必须是非空数组');
      }
      
      // 验证红球范围
      for (let ball of redBalls) {
        if (!ball || ball < 1 || ball > 33) {
          throw new Error('红球必须是1-33之间的数字');
        }
      }
      
      // 验证蓝球范围
      for (let ball of blueBalls) {
        if (!ball || ball < 1 || ball > 16) {
          throw new Error('蓝球必须是1-16之间的数字');
        }
      }
      
      // 检查红球是否有重复
      const uniqueRedBalls = [...new Set(redBalls)];
      if (uniqueRedBalls.length !== 6) {
        throw new Error('红球不能有重复数字');
      }
      
      const sourceName = {
        'smart_picker': '智能选号',
        'calculator': '计算器',
        'manual': '手动选择'
      }[source] || '未知来源';
      
      const record = {
        redBalls: redBalls.sort((a, b) => a - b), // 排序存储
        blueBalls: blueBalls.sort((a, b) => a - b), // 排序存储
        algorithm: algorithm || 'unknown',
        source: source,
        sourceName: sourceName,
        timestamp: new Date().toISOString(), // 统一使用ISO格式
        createTime: new Date().toISOString(),
        note: note || '',
        predicted: predicted || false
      };
      
      const id = await db.pickingRecords.add(record);
      return { id, ...record };
    } catch (error) {
      console.error('添加选号记录失败:', error);
      throw error;
    }
  }

  /**
   * 获取所有选号记录
   */
  static async getAllPickingRecords() {
    try {
      const records = await db.pickingRecords.orderBy('createTime').reverse().toArray();
      return records;
    } catch (error) {
      console.error('获取选号记录失败:', error);
      throw error;
    }
  }

  /**
   * 删除选号记录
   */
  static async deletePickingRecord(id) {
    try {
      await db.pickingRecords.delete(id);
      return true;
    } catch (error) {
      console.error('删除选号记录失败:', error);
      throw error;
    }
  }

  /**
   * 清理异常时间数据
   */
  static async cleanupAbnormalTimeData() {
    try {
      console.log('开始清理异常时间数据...');
      
      // 获取所有选号记录
      const allRecords = await db.pickingRecords.toArray();
      let cleanedCount = 0;
      
      for (const record of allRecords) {
        let needUpdate = false;
        const updates = {};
        
        // 检查并修复timestamp
        if (record.timestamp && typeof record.timestamp === 'string') {
          const timestampDate = new Date(record.timestamp);
          if (isNaN(timestampDate.getTime()) || timestampDate.getFullYear() < 2020) {
            updates.timestamp = new Date().toISOString();
            needUpdate = true;
          }
        } else if (!record.timestamp) {
          updates.timestamp = new Date().toISOString();
          needUpdate = true;
        }
        
        // 检查并修复createTime
        if (record.createTime && typeof record.createTime === 'string') {
          const createTimeDate = new Date(record.createTime);
          if (isNaN(createTimeDate.getTime()) || createTimeDate.getFullYear() < 2020) {
            updates.createTime = new Date().toISOString();
            needUpdate = true;
          }
        } else if (!record.createTime) {
          updates.createTime = new Date().toISOString();
          needUpdate = true;
        }
        
        if (needUpdate) {
          await db.pickingRecords.update(record.id, updates);
          cleanedCount++;
        }
      }
      
      console.log(`清理完成，共修复 ${cleanedCount} 条异常时间数据`);
      return cleanedCount;
    } catch (error) {
      console.error('清理异常时间数据失败:', error);
      throw error;
    }
  }

  /**
   * 清空所有选号记录
   */
  static async clearAllPickingRecords() {
    try {
      await db.pickingRecords.clear();
      return true;
    } catch (error) {
      console.error('清空选号记录失败:', error);
      throw error;
    }
  }

  /**
   * 根据来源获取选号记录
   */
  static async getPickingRecordsBySource(source) {
    try {
      const records = await db.pickingRecords.where('source').equals(source).toArray();
      // 按创建时间倒序排列
      return records.sort((a, b) => new Date(b.createTime) - new Date(a.createTime));
    } catch (error) {
      console.error('获取选号记录失败:', error);
      throw error;
    }
  }

  /**
   * 数据迁移：将localStorage数据迁移到数据库
   */
  static async migrateLocalStorageData() {
    try {
      let migratedCount = 0;
      
      // 迁移智能选号记录
      const smartPickingHistory = JSON.parse(localStorage.getItem('lotteryPickHistory') || '[]');
      for (const record of smartPickingHistory) {
        if (record.numbers && record.numbers.red && record.numbers.blue) {
          await this.addPickingRecord(
            record.numbers.red,
            record.numbers.blue,
            record.algorithm || 'unknown',
            'smart_picker',
            record.note || '',
            record.predicted || false
          );
          migratedCount++;
        }
      }
      
      // 迁移手动选择记录
      const manualHistory = JSON.parse(localStorage.getItem('manualPickHistory') || '[]');
      for (const record of manualHistory) {
        if (record.numbers && record.numbers.red && record.numbers.blue) {
          await this.addPickingRecord(
            record.numbers.red,
            record.numbers.blue,
            record.algorithm || 'manual',
            'manual',
            record.note || '',
            record.predicted || false
          );
          migratedCount++;
        }
      }
      
      console.log(`成功迁移 ${migratedCount} 条记录到数据库`);
      return migratedCount;
    } catch (error) {
      console.error('数据迁移失败:', error);
      throw error;
    }
  }

  // ==================== 数据库健康检查 ====================
  static async healthCheck() {
    try {
      console.log('开始数据库健康检查...');
      
      const result = {
        isConnected: false,
        recordCount: 0,
        selectionCount: 0,
        lastRecord: null,
        errors: []
      };
      
      try {
        // 检查连接
        result.recordCount = await this.getLotteryHistoryCount();
        result.selectionCount = await this.getSelectionCount();
        result.isConnected = true;
        
        // 获取最新记录
        const records = await this.getAllLotteryHistory();
        if (records.length > 0) {
          result.lastRecord = records[0];
        }
        
      } catch (error) {
        result.errors.push(`数据库连接错误: ${error.message}`);
      }
      
      console.log('数据库健康检查结果:', result);
      return result;
    } catch (error) {
      console.error('数据库健康检查失败:', error);
      throw error;
    }
  }
}

export default DatabaseService;